<?php
tsLoad('/core/CoreLib/AbstractMysqlPage.class.php');
class SysUserPage extends AbstractMysqlPage {
	/**
	 * 取得总的行数
     */
    protected function calTotalRows ()
    {
        global $mysqlTools;
        $condition = $mysqlTools->check_input($this->condition,array('userLoginId'));
        
       	$sqlCore = "
        	SELECT 
			    COUNT(DISTINCT su.userLoginId) AS totalCount
			FROM
			    sys_user su left join sys_user_role sur on su.userId=sur.userId 
				left join sys_role sr on sur.roleId=sr.roleId
        ";
        
        $where = "
         	WHERE (sr.roleType=1 or sr.roleType is null)
        ";
        
    	//用户信息查询
        if(!empty($condition['contact'])){
        	$where.= ' AND  (`su`.`userEmail` = '.$condition['contact'].
        	' OR `su`.`userTel` = '.$condition['contact'].
        	' OR `su`.`userPhone` = '.$condition['contact'].
        	' OR `su`.`userQQ` = '.$condition['contact'].' )';
        }
        
        //用户名查询
        if(!empty($condition['userLoginId'])){
        	$where.= ' AND  su.userLoginId LIKE '.$condition['userLoginId'];
        }
        
        //查看状态
        if(!empty($condition['userState']) && $condition['userState']!=4){
        	$where.= ' AND su.userState ='.$condition['userState'];
        }
        
        //创建开始时间与结束时间
        if(!empty($condition['userCreateTimeStart'])){
        	$where.= ' AND su.userCreateTime >='.$condition['userCreateTimeStart'];
        }
        if(!empty($condition['userCreateTimeEnd'])){
        	$where.= ' AND su.userCreateTime ='.$condition['userCreateTimeEnd'];
        }
        
        $sql = $sqlCore.$where;
        //总记录数
        $totalRows = $mysqlTools->selectSql($sql)[0]['totalCount'];
        
        return $totalRows;
    }

	/** 
     *取得数据集合
     */
    protected function getList ($sRowNo, $pageRows)
    {
        global $mysqlTools;
        
        //保存一个原生值以便需要的时候进行处理
        $orgCondition = $this->condition;
        
        $condition = $mysqlTools->check_input($this->condition,array('userLoginId'));
        
        $sqlCore = "
        	SELECT 
		    DISTINCT su.userId,
		    su.userLoginId,
		    su.userPhone,
		    su.userTel,
		    su.userEmail,
		    su.userQQ,
		    su.userAvatarId,
		    su.userState,
		    su.userCreateTime,
        	su.userSex
		FROM
		    sys_user su left join sys_user_role sur on su.userId=sur.userId 
			left join sys_role sr on sur.roleId=sr.roleId
        ";
        
        $where = "
         	WHERE (sr.roleType=1 or sr.roleType is null)
        ";
        
        //用户信息查询
        if(!empty($condition['contact'])){
        	$where.= ' AND  (`su`.`userEmail` = '.$condition['contact'].
        	' OR `su`.`userTel` = '.$condition['contact'].
        	' OR `su`.`userPhone` = '.$condition['contact'].
        	' OR `su`.`userQQ` = '.$condition['contact'].' )';
        }
        
        //用户名查询
        if(!empty($condition['userLoginId'])){
        	$where.= ' AND  su.userLoginId LIKE '.$condition['userLoginId'];
        }
        
        //查看状态
        if(!empty($condition['userState']) && $condition['userState']!=4){
        	$where.= ' AND su.userState ='.$condition['userState'];
        }
        
        //创建开始时间与结束时间
        if(!empty($condition['userCreateTimeStart'])){
        	$where.= ' AND su.userCreateTime >='.$condition['userCreateTimeStart'];
        }
        if(!empty($condition['userCreateTimeEnd'])){
        	$where.= ' AND su.userCreateTime ='.$condition['userCreateTimeEnd'];
        }
        $order = "";
        if(!empty($orgCondition['order'])&&!empty($orgCondition['sort'])){
        	$order = " ORDER BY su.".$orgCondition['sort']." ".$orgCondition['order']." ";
        }else{
	        $order = " ORDER BY `su`.`userCreateTime` DESC ";
        }
        $limit = " LIMIT $sRowNo,$pageRows";
        $sql = $sqlCore.$where.$order.$limit;
        
        //查询角色信息分页
        $list = $mysqlTools->selectSql($sql);
        
        return $list;
    }
    
    /**
     *通过loginId查询用户是否存在
     */
    public function getUserByLoginId ($condition)
    {
    	global $mysqlTools;
    
    	$condition = $mysqlTools->check_input($condition);
    
    	$sql = "
    	SELECT
    	su.userId,
    	su.userLoginId,
    	su.userPwd,
    	su.userPhone,
    	su.userTel,
    	su.userEmail,
    	su.userQQ,
    	su.userAvatarId,
    	su.userState,
    	su.userCreateTime,
    	su.userSex
    	FROM
    	sys_user su 
    	where
    	su.userLoginId = $condition[userLoginId]
    	";
    
    	//查询用户信息
    	$list = $mysqlTools->selectSql($sql);
    	if(!empty($list))
    		return $list[0];
    	else
    		return array();
    }
    
    /**
     *取得用户信息
     */
    public function getUserById ($condition)
    {
    	global $mysqlTools;
    
    	$condition = $mysqlTools->check_input($condition);
    
    	$sql = "
        	SELECT
				su.userId,
				su.userLoginId,
				su.userPwd,
				su.userPhone,
				su.userTel,
				su.userEmail,
				su.userQQ,
				su.userAvatarId,
				su.userState,
				su.userCreateTime,
				su.userSex,
				sp.photoUrl,
				sp.photoNoExtUrl,
				sp.photoExt
			FROM
				sys_user su left join sys_photo sp on su.userAvatarId=sp.photoId
			where 
				su.userId = $condition[userId]
        ";
    
    	//查询用户信息
    	$list = $mysqlTools->selectSql($sql);
    	if(!empty($list))
    		return $list[0];
    	else
    	 	return array();
    }
    
    /**
     *取得用户角色信息
     */
    public function getUserRoleById ($condition)
    {
    	global $mysqlTools;
    
    	$condition = $mysqlTools->check_input($v);
    
    	$sql = "
        	select  `sr`.`name` as `roleName` FROM `sys_role` `sr`
		        INNER JOIN
		    `sys_user_role` `sur` ON `sur`.`roleId` = `sr`.`id`
    		WHERE `sur`.`userId` = $v->get[userId]
        ";
    
    	//查询用户信息
    	$list = $mysqlTools->selectSql($sql);
    	if(!empty($list))
    		return $list;
    	else
    		return array();
    }
    
    //增加一个用户
    public function addUser($condition){
    	global $mysqlTools;
    	//生成唯一ID
    	$uniqid = getCurrentId();
    
    	$condition['userPwd'] = md5($condition['userPwd']);
    	
    	//参数处理注入SQL安全问题
    	$condition = $mysqlTools->check_input($condition);
    	//处理null值,如果值为null数据库也置为null
    	$condition = $mysqlTools->check_null($condition);
    	try {
    		$mysqlTools->startTransaction();
    		$result = $mysqlTools->executeSql("
    				insert into sys_user (userId,userLoginId,userPwd,userPhone,userTel,userQQ,userEmail,userAvatarId,userState,userSex,userCreateTime)
    				values($uniqid,$condition[userLoginId],$condition[userPwd],$condition[userPhone],$condition[userTel],$condition[userQQ],$condition[userEmail],
    				null,1,$condition[userSex],now())
    				");
    		$mysqlTools->commitTransaction();
    	} catch (\Exception $e) {
    		$mysqlTools->rollback();
    	}
    	return $result;
    }
    
    /**
	 * 添加一个用户角色信息
	 */
	public function addUserRoles($condition){
		global $mysqlTools;
			
		//参数处理注入SQL安全问题
		//$condition = $mysqlTools->check_input($condition);
		try {
			$mysqlTools->startTransaction();
			//首先删除以前的该用户角色
			$result = $mysqlTools->executeSql("
					delete from sys_user_role where userId = $condition[userId];
					");
			//取出角色菜单数组后进行循环插入
			if(!empty($condition['userRoleIds'])){
				$roleIds = explode(",",$condition['userRoleIds']);
				foreach ($roleIds as $roleId) {
					if(is_numeric($roleId)){
						$result = $result&&$mysqlTools->executeSql("insert into sys_user_role(userId,roleId) values($condition[userId],$roleId);");
					}
				}
			}
	
			if($result)
				$mysqlTools->commitTransaction();
			else
				$mysqlTools->rollback();
		} catch (\Exception $e) {
			$mysqlTools->rollback();
			return "数据库插入发生异常";
		}
		return $result;
	}
    
    /**
     * 删除用户菜单
     */
    public function delUserMenu($condition){
    	global $mysqlTools;
    	//参数处理注入SQL安全问题
    	$result = true;
    	if(array_key_exists('del',$condition) && empty($condition['del'])){
    		return $result;
    	}
    	$list = explode(',',$condition['del']);
    	$condition = $mysqlTools->check_input($condition);
    	if(!empty($condition['del'])){
    		foreach ($list as $menuId){
    			$res = $mysqlTools->executeSql("
    					DELETE FROM `sys_user_menu`
    					WHERE `menuId` = $menuId AND `userId` = $condition[userId]
    					");
    			if(!$res){
    				$result = false;
    			}
    		}
    	}
    	return $result;
    }
    
    /**
     * 修改用户
     */
    public function updateUser($condition){
    	global $mysqlTools;
    	//参数处理注入SQL安全问题
    	if(!empty($condition['userPwd']))
    		$pwd = $condition['userPwd'];
    	$condition = $mysqlTools->check_input($condition);
    	try {
    		$sql = "UPDATE `sys_user` SET `userId` = `userId`";
    		if(!empty($condition['userLoginId'])){
           		$sql.= ",`userLoginId` = $condition[userLoginId]";
    		}
    		if(!empty($pwd)){
    			$pwd = md5($pwd);
    			$sql.= ",`userPwd` = '$pwd'";
    		}
    		if(!empty($condition['userPhone'])){
    			$sql.= ",`userPhone` = $condition[userPhone]";
    		}
    		if(!empty($condition['userSex'])){
    			$sql.= ",`userSex` = $condition[userSex]";
    		}
    		if(!empty($condition['userTel'])){
    			$sql.= ",`userTel` = $condition[userTel]";
    		}
    		if(!empty($condition['userEmail'])){
    			$sql.= ",`userEmail` = $condition[userEmail]";
    		}
    		if(!empty($condition['userQQ'])){
    			$sql.= ",`userQQ` = $condition[userQQ]";
    		}
    		if(!empty($condition['userAvatarId'])){
    			$sql.= ",`userAvatarId` = $condition[userAvatarId]";
    		}
    		if(isset($condition['userState'])){
    			$sql.= ",`userState` = $condition[userState]";
    		}
    		$sql.=" where `userId` = $condition[userId]";
    		$mysqlTools->startTransaction();
    		$result = $mysqlTools->executeSql($sql);
    		$mysqlTools->commitTransaction();
    	} catch (\Exception $e) {
    		$mysqlTools->rollback();
    	}
    	return $result;
    }
    
    /**
     * 查询名称是否存在
     */
    public function checkUserName($condition){
    	global $mysqlTools;
    	//参数处理注入SQL安全问题
    	$condition = $mysqlTools->check_input($condition);
    	try {
    		$mysqlTools->startTransaction();
    		$sql = 'SELECT COUNT(*) as count FROM `sys_user` ';
    		$sql.= ' WHERE `userLoginId` = '.$condition['userLoginId'];
    		if(!empty($condition['userId'])){
    			$sql.=' AND `userId` <> '.$condition['userId'];
    		}
    		$result = $mysqlTools->selectSql($sql)[0]['count'];
    		$mysqlTools->commitTransaction();
    	} catch (\Exception $e) {
    		$mysqlTools->rollback();
    		return false;
    	}
    	return $result==0;
    }
    
    /**
     * 处理菜单操作
     */
    public function processUserMenus($condition)
    {
    	global $mysqlTools;
    	try {
	    	$mysqlTools->startTransaction();
	    	$result = $this->addUserMenu($condition) && $this->delUserMenu($condition);
	    	if($result){
	    		$mysqlTools->commitTransaction();
	    	}else{
	    		$mysqlTools->rollback();
	    	}
    	} catch (\Exception $e) {
    		$mysqlTools->rollback();
    		return $result;
    	}
    	return $result;
    }
}	

?>